﻿Imports System.Data.SqlClient

Public Class BudgetsFormV2

    Private CurrentPaymentID As String = ""

    Private Sub BudgetsFormV2_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
        WhatTypeLabel.Text = ""
        PeriodBox.zConnectionString = StoreConnect

        PeriodBox.zSelectedPeriod_ID = CurrentPeriodID

        ProviderBox.zConnectionString = StoreConnect
        LoadBudgetBox()

    End Sub

    Private Sub LoadBudgetBox()

        Dim sql As String

        sql = "select * from Budgets order by Budget_ID"

        LoadComboBox(BudgetBox, sql, "Budget_ID", "Budget_Description")

    End Sub

    Private Sub LoadAllocationBox()

        Dim sql As String

        Try
            sql = "select * from Allocation_Lines where Budget_ID = '" & BudgetBox.SelectedValue & "' order by Allocation_ID"
            LoadComboBox(AllocationBox, sql, "Allocation_ID", "Allocation_Description")
        Catch ex As Exception

        End Try



    End Sub

    Private Sub LoadCustomBox()

        Dim sql As String

        Try
            sql = "select Pattern_ID, Pattern_Description from Pattern_IDs where Allocation_ID = '_CUST' and Period_ID = " & PeriodBox.zSelectedPeriod_ID & " and Budget_ID = '" & BudgetBox.SelectedValue & "'"
            LoadComboBox(CustomPatternBox, sql, "Pattern_ID", "Pattern_Description")
        Catch ex As Exception
            Exit Sub
        End Try

        If CustomPatternBox.Items.Count = 0 Then

            CustomRepeater.DataSource = Nothing

        End If

    End Sub

    Private Sub LoadAndBindRepeater()

        Dim TheData As DataView

        Dim sql As String

        Try
            sql = "exec spBudget_national_Pattern @Period_ID = " & PeriodBox.zSelectedPeriod_ID & ", @Allocation_ID = '" & AllocationBox.SelectedValue & "'"
            TheData = LoadDataView(sql)
        Catch ex As Exception
            Exit Sub
        End Try

        NationalPatternLabel.DataBindings.Clear()
        NationalPatternLabel.DataBindings.Add(New System.Windows.Forms.Binding("Text", TheData, "Friendly_String", True))

        NationalRepeater.DataSource = TheData

        sql = "select Payment_ID, Payment_Description from  Payment_Types where Payment_ID in (select Payment_ID from Pattern_IDs where Period_ID = " & PeriodBox.zSelectedPeriod_ID & " and Allocation_ID = '" & AllocationBox.SelectedValue & "')"

        Try
            Dim dv As DataView
            dv = LoadDataView(sql)
            PaymentTypeLabel.Text = dv(0)("Payment_Description").ToString
            CurrentPaymentID = dv(0)("Payment_ID").ToString
        Catch ex As Exception
            PaymentTypeLabel.Text = ""
            CurrentPaymentID = ""
        End Try


    End Sub

    Private Sub LoadAndBindCustomRepeater()

        Dim TheData As DataView

        Dim sql As String



        Try
            sql = "exec dbo.spBudget_Custom_Pattern '" & CustomPatternBox.SelectedValue & "'"
            TheData = LoadDataView(sql)
        Catch ex As Exception
            TheData = Nothing
            CustomLabel.DataBindings.Clear()
            CustomRepeater.DataSource = TheData
            Exit Sub
        End Try

        CustomLabel.DataBindings.Clear()
        CustomLabel.DataBindings.Add(New System.Windows.Forms.Binding("Text", TheData, "Friendly_String", True))

        CustomRepeater.DataSource = TheData


    End Sub

    Private Sub BudgetBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BudgetBox.SelectedIndexChanged
        NationalRepeater.DataSource = Nothing
        LoadAllocationBox()
        LoadCustomBox()
    End Sub


    Private Sub AllocationBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AllocationBox.SelectedIndexChanged
        LoadAndBindRepeater()
        LoadAndBindProvPatterns()
    End Sub

    Private Sub PeriodBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PeriodBox.SelectedIndexChanged
        LoadAndBindRepeater()
        LoadAndBindProvPatterns()
        LoadCustomBox()
    End Sub

    Private Sub NationalRepeater_DrawItem(ByVal sender As System.Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemEventArgs) Handles NationalRepeater.DrawItem

        Dim TheLabel As Label = TryCast(e.DataRepeaterItem.Controls("NationalPatternLabel"), Label)

        If Not TheLabel Is Nothing Then

            TheLabel.Text = TheLabel.Text.Replace("{LF}", vbLf)

            If NationalRepeater.DataSource(e.DataRepeaterItem.ItemIndex)("Frozen") = 1 Then
                TheLabel.BackColor = Color.LightBlue
            Else
                TheLabel.BackColor = Color.Transparent
            End If

        End If

    End Sub


    Public Sub LoadAndBindProvPatterns()
        Dim sql As String
        Dim TheData As DataView

        If ProviderBox.zSelectedProvider.ProviderName = "" Then
            ProviderPatternRepeater.DataSource = Nothing
            WhatTypeLabel.Text = ""
            Exit Sub
        End If

        Try
            sql = "exec  dbo.spBudget_Provider_Pattern " & PeriodBox.zSelectedPeriod_ID & ", '" & AllocationBox.SelectedValue & "', '" & ProviderBox.zSelectedProvider.RID & "'"

            TheData = LoadDataView(sql)

        Catch ex As Exception
            Exit Sub
        End Try

        If TheData.Count <> 0 Then
            'provider pattern
            ProviderPatternLabel.DataBindings.Clear()
            ProviderPatternLabel.DataBindings.Add(New System.Windows.Forms.Binding("Text", TheData, "Friendly_String"))

            ProviderPatternRepeater.DataSource = TheData

            If TheData(0)("Provider_Pattern") = 1 Then
                WhatTypeLabel.Text = "Custom Provider Pattern"
            Else
                WhatTypeLabel.Text = "Based on National Pattern"
            End If
        Else
            ProviderPatternRepeater.DataSource = Nothing
            WhatTypeLabel.Text = ""
        End If

    End Sub

    Private Sub BudgetButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BudgetButton.Click
        BudgetsMenu.Show(BudgetButton, New System.Drawing.Point(0, BudgetButton.Height))
    End Sub


    Private Sub AllocationButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AllocationButton.Click
        AllocationMenu.Show(AllocationButton, New System.Drawing.Point(0, AllocationButton.Height))
    End Sub

    Private Sub NatPatternButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NatPatternButton.Click
        NationalPatternMenu.Show(NatPatternButton, New System.Drawing.Point(0, NatPatternButton.Height))
    End Sub

    Private Sub ProviderBox_ProviderSelected(ByVal e As TheStore.ProviderSearchBoxCtrl.SelectedProviderClass) Handles ProviderBox.ProviderSelected
        LoadAndBindProvPatterns()

    End Sub

    Private Sub ProviderPatternRepeater_DrawItem(ByVal sender As System.Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemEventArgs) Handles ProviderPatternRepeater.DrawItem
        Dim TheLabel As Label = TryCast(e.DataRepeaterItem.Controls("ProviderPatternLabel"), Label)

        If Not TheLabel Is Nothing Then

            TheLabel.Text = TheLabel.Text.Replace("{LF}", vbLf)

            If ProviderPatternRepeater.DataSource(e.DataRepeaterItem.ItemIndex)("Frozen") = 1 Then
                TheLabel.BackColor = Color.LightBlue
            Else
                TheLabel.BackColor = Color.Transparent
            End If

        End If
    End Sub


    Private Sub ProviderButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProviderButton.Click
        ProviderMenu.Show(ProviderButton, New System.Drawing.Point(0, ProviderButton.Height))
    End Sub

    Private Sub ProviderMenu_Opening(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles ProviderMenu.Opening
        If ProviderBox.zSelectedProvider.ProviderName = "" Then
            e.Cancel = True

        End If

        If ProviderPatternRepeater.ItemCount = 0 Then e.Cancel = True


        If Not WhatTypeLabel.Text.Contains("Provider") Then
            AddProviderItem.Visible = True
            EditProviderItem.Visible = False
            DeleteProviderItem.Visible = False
        Else
            AddProviderItem.Visible = False
            EditProviderItem.Visible = True
            DeleteProviderItem.Visible = True
        End If


    End Sub

    Private Sub NationalPatternMenu_Opening(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles NationalPatternMenu.Opening
        If AllocationBox.Items.Count = 0 Then e.Cancel = True

        If NationalRepeater.ItemCount <> 0 Then
            AddNationalItem.Visible = False
            EditNationalItem.Visible = True
        Else
            AddNationalItem.Visible = True
            EditNationalItem.Visible = False
        End If
    End Sub

    Private Sub ProviderBox_ProviderDeselected() Handles ProviderBox.ProviderDeselected
        ProviderPatternRepeater.DataSource = Nothing
        WhatTypeLabel.Text = ""
    End Sub

    Private Sub ProviderPatternRepeater_ItemTemplate_MouseDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ProviderPatternRepeater.ItemTemplate.MouseDoubleClick

        Dim i As Integer
        Dim t As String

        i = ProviderPatternRepeater.CurrentItem.ItemIndex

        t = DirectCast(ProviderPatternRepeater.DataSource, DataView)(i)("Month_Total").ToString

        MessageBox.Show(t)

    End Sub


    Private Sub CustomButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomButton.Click
        CustomMenu.Show(CustomButton, New System.Drawing.Point(0, CustomButton.Height))
    End Sub

    Private Sub CustomMenu_Opening(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles CustomMenu.Opening
        If NationalRepeater.ItemCount = 0 Then e.Cancel = True

        If CustomPatternBox.Items.Count = 0 Then
            ApplyCustomPatternItem.Visible = False
            AddCustomPatternItem.Visible = True
            EditCustomPatternItem.Visible = False
            DeleteCustomPatternItem.Visible = False
            CustomSeperatorItem.Visible = False

        Else
            ApplyCustomPatternItem.Visible = True
            AddCustomPatternItem.Visible = True
            EditCustomPatternItem.Visible = True
            DeleteCustomPatternItem.Visible = True
            CustomSeperatorItem.Visible = True
        End If
    End Sub

    Private Sub AddCustomPattern()

        If CurrentPaymentID = "" Then Exit Sub

        Dim Description As String = "_REPEAT"

        While Description = "_REPEAT"
            Description = InputBox("Please enter a description for your custom pattern", "Adding custom pattern...", "")

            If Len(Description) > 50 Then
                Description = "_REPEAT"
                MessageBox.Show("The description cannot be more than 50 characters long, please enter a description that is not more than 50 characters long.", "Please try again", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
        End While

        If Description = "" Then Exit Sub

        'insert an ID

        Dim sql As String

        sql = "insert into Pattern_IDs (Pattern_ID, Budget_ID, Allocation_ID, Period_ID, Pattern_Description, Payment_ID) " & _
            "values (@Pattern_ID, @Budget_ID, @Allocation_ID, @Period_ID, @Pattern_Description, @Payment_ID)"

        Dim sp As New List(Of SqlParameter)
        Dim custID As String = GetNextCustID()

        sp.Add(New SqlParameter("@Pattern_ID", custID))
        sp.Add(New SqlParameter("@Budget_ID", BudgetBox.SelectedValue))
        sp.Add(New SqlParameter("@Allocation_ID", "_CUST"))
        sp.Add(New SqlParameter("@Period_ID", PeriodBox.zSelectedPeriod_ID))
        sp.Add(New SqlParameter("@Pattern_Description", Description))
        sp.Add(New SqlParameter("@Payment_ID", CurrentPaymentID))

        If RunNonQueryParams(sql, sp) = "" Then
            'load with national pattern
            sql = "insert into Patterns select '" & custID & "' as Pattern_ID, Month, Percentage  from Patterns where Pattern_ID = " & _
                    "(select Pattern_ID from Pattern_IDs where Allocation_ID = '" & AllocationBox.SelectedValue & "' and Period_ID = " & PeriodBox.zSelectedPeriod_ID & ") "
            RunNonQuery(sql)

            LoadCustomBox()
            CustomPatternBox.SelectedValue = custID
        End If

    End Sub

    Private Function GetNextCustID() As String
        Dim sql As String
        Dim dv As DataView

        sql = "select isnull(max(case when SUBSTRING(Pattern_ID,1,2) = '' " & _
                "then 0 else cast(SUBSTRING(Pattern_ID,1,2) as int) end),0) as ID " & _
                "from Pattern_IDs where Allocation_ID   = '_CUST' and Budget_ID = '" & BudgetBox.SelectedValue & "' and Period_ID = " & PeriodBox.zSelectedPeriod_ID
        dv = LoadDataView(sql)

        Dim s As String

        s = dv(0)("ID") + 1

        Select Case Len(s)
            Case 1
                s = "0" & s
            
        End Select

        s = s & Mid(PeriodBox.zSelectedPeriod_ID, 3, 2) & Mid(BudgetBox.SelectedValue, 5, 1)

        Return s
    End Function

    Private Sub AddCustomPatternItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddCustomPatternItem.Click
        AddCustomPattern()
    End Sub

    Private Sub CustomPatternBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomPatternBox.SelectedIndexChanged
        LoadAndBindCustomRepeater()

    End Sub

    Private Sub CustomRepeater_DrawItem(ByVal sender As System.Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemEventArgs) Handles CustomRepeater.DrawItem
        Dim TheLabel As Label = TryCast(e.DataRepeaterItem.Controls("CustomLabel"), Label)

        If Not TheLabel Is Nothing Then

            TheLabel.Text = TheLabel.Text.Replace("{LF}", vbLf)

          

        End If
    End Sub

    Private Sub AddNationalItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddNationalItem.Click
        AddNationalPattern()

    End Sub

    Private Sub EditNationalPattern()
        Dim Errors As String = ""

        With PatternDialogV2
            .Period_ID = PeriodBox.zSelectedPeriod_ID
            .Allocation_ID = AllocationBox.SelectedValue
            .Payment_ID = CurrentPaymentID
            .RID = ""
            .IsCustom = False

            If .ShowDialog() = Windows.Forms.DialogResult.OK Then

                Dim r As DataRowView
                Dim p As New List(Of SqlParameter)
                Dim sql As String

                Dim Frozen As Boolean = True

                For Each r In .TheRepeater.DataSource

                    If r("Frozen") = 0 Then

                        Frozen = False
                        sql = "update Patterns set Percentage = @Percentage where Pattern_ID = @Pattern_ID and Month = @Month"

                        p.Clear()
                        p.Add(New SqlParameter("Pattern_ID", r("Pattern_ID")))
                        p.Add(New SqlParameter("Month", r("Month")))
                        p.Add(New SqlParameter("Percentage", r("Percentage_String").ToString.Replace("%", "") / 100))

                        Errors = RunNonQueryParams(sql, p)

                        If Errors <> "" Then
                            MessageBox.Show(Errors, "There was an error", MessageBoxButtons.OK, MessageBoxIcon.Error)

                            .Close()
                            Exit Sub

                        End If
                    End If
                    
                Next

                If Frozen = False Then
                    UpdateNationalPayments()
                End If

            End If

            .Close()
        End With

        If Errors = "" Then
            LoadAndBindRepeater()
        End If


    End Sub

    Private Sub AddNationalPattern()
        Dim Errors As String = ""

        With PatternDialogV2
            .Period_ID = PeriodBox.zSelectedPeriod_ID
            .Allocation_ID = AllocationBox.SelectedValue
            .Payment_ID = ""
            .RID = ""
            .IsCustom = False

            If .ShowDialog() = Windows.Forms.DialogResult.OK Then

                Dim Pattern_ID As String = ""

                Pattern_ID = Mid(AllocationBox.SelectedValue, 3, 3) & Mid(PeriodBox.zSelectedPeriod_ID, 3, 2)

                'insert into the pattern_IDs table
                Dim sql As String

                sql = "insert into Pattern_IDs (Pattern_ID, Budget_ID, Allocation_ID, Period_ID, Pattern_Description, Payment_ID) " & _
                      "Values (@Pattern_ID, @Budget_ID, @Allocation_ID, @Period_ID, @Pattern_Description, @Payment_ID)"

                Dim p As New List(Of SqlParameter)


                p.Clear()
                p.Add(New SqlParameter("@Pattern_ID", Pattern_ID))
                p.Add(New SqlParameter("@Budget_ID", BudgetBox.SelectedValue))
                p.Add(New SqlParameter("@Allocation_ID", AllocationBox.SelectedValue))
                p.Add(New SqlParameter("@Period_ID", PeriodBox.zSelectedPeriod_ID))
                p.Add(New SqlParameter("@Pattern_Description", Mid("National Pattern for " & AllocationBox.SelectedValue, 1, 50)))
                p.Add(New SqlParameter("@Payment_ID", .PaymentTypeBox.SelectedValue))

                Errors = RunNonQueryParams(sql, p)

                If Errors = "" Then

                    'now add the pattern
                    Dim r As DataRowView

                    For Each r In .TheRepeater.DataSource

                        sql = "insert into patterns (Pattern_ID, Month, Percentage) Values (@Pattern_ID, @Month, @Percentage)"

                        p.Clear()
                        p.Add(New SqlParameter("Pattern_ID", Pattern_ID))
                        p.Add(New SqlParameter("Month", r("Month")))
                        p.Add(New SqlParameter("Percentage", r("Percentage_String").ToString.Replace("%", "") / 100))

                        Errors = RunNonQueryParams(sql, p)

                        If Errors <> "" Then
                            MessageBox.Show(Errors, "There was an error", MessageBoxButtons.OK, MessageBoxIcon.Error)

                            'undo the pattern_id insert
                            sql = "delete from Pattern_IDs where Pattern_ID = '" & Pattern_ID & "' and Period_id = " & PeriodBox.zSelectedPeriod_ID

                            RunNonQuery(sql)

                            .Close()
                            Exit Sub

                        End If
                    Next

                    UpdateNationalPayments()

                Else
                    MessageBox.Show(Errors, "There was an error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End If

            End If

            .Close()

            If Errors = "" Then
                LoadAndBindRepeater()

            End If
        End With

    End Sub

    Private Sub EditNationalItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EditNationalItem.Click
        EditNationalPattern()

    End Sub

    Private Sub AddProviderPattern()
        Dim Errors As String = ""

        'check first if everything has already been paid
        'Dim FrozenPayments As Decimal = 0
        'Dim TotalPayments As Decimal = 0
        Dim r As DataRowView
        Dim sql As String
        Dim p As New List(Of SqlParameter)

        'For Each r In ProviderPatternRepeater.DataSource
        '    If r("Frozen") = 1 Then

        '        FrozenPayments = FrozenPayments + r("Month_Total")

        '    End If
        '    TotalPayments = TotalPayments + r("Month_Total")
        'Next

        'If FrozenPayments >= TotalPayments Then

        '    MessageBox.Show("All possible payments have been frozen.  If you need to update the pattern either unfreeze payments or update the allocation first", "Cannot edit pattern", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        '    Exit Sub
        'End If

        'create a provider pattern based on the national pattern

        For Each r In ProviderPatternRepeater.DataSource

            sql = "insert into Provider_Patterns (Period_ID, Allocation_ID, RID, Month, Percentage) Values (@Period_ID, @Allocation_ID, @RID, @Month, @Percentage)"

            p.Clear()
            p.Add(New SqlParameter("@Period_ID", PeriodBox.zSelectedPeriod_ID))
            p.Add(New SqlParameter("@Allocation_ID", AllocationBox.SelectedValue))
            p.Add(New SqlParameter("@RID", ProviderBox.zSelectedProvider.RID))
            p.Add(New SqlParameter("Month", r("Month")))
            p.Add(New SqlParameter("Percentage", r("Percentage")))

            RunNonQueryParams(sql, p)

        Next

        With PatternDialogV2
            .Period_ID = PeriodBox.zSelectedPeriod_ID
            .Allocation_ID = AllocationBox.SelectedValue
            .Payment_ID = ""
            .RID = ProviderBox.zSelectedProvider.RID
            .IsCustom = False

            If .ShowDialog() = Windows.Forms.DialogResult.OK Then



                Dim SafetyRID As String = Format(Now(), "ddhhmmssfff")

                For Each r In .TheRepeater.DataSource


                    sql = "insert into Provider_Patterns (Period_ID, Allocation_ID, RID, Month, Percentage) Values (@Period_ID, @Allocation_ID, @RID, @Month, @Percentage)"

                    p.Clear()
                    p.Add(New SqlParameter("@Period_ID", PeriodBox.zSelectedPeriod_ID))
                    p.Add(New SqlParameter("@Allocation_ID", AllocationBox.SelectedValue))
                    p.Add(New SqlParameter("@RID", SafetyRID))
                    p.Add(New SqlParameter("Month", r("Month")))
                    p.Add(New SqlParameter("Percentage", r("Percentage_String").ToString.Replace("%", "") / 100))

                    Errors = RunNonQueryParams(sql, p)

                    If Errors <> "" Then

                        sql = "delete from provider_patterns where RID = '" & SafetyRID & "'"
                        RunNonQuery(sql)

                        MessageBox.Show(Errors, "There was an error", MessageBoxButtons.OK, MessageBoxIcon.Error)

                        .Close()
                        Exit Sub

                    End If


                Next

                'all good so replace
                sql = "delete from Provider_patterns where Period_ID = @Period_ID and Allocation_ID = @Allocation_ID and RID = @RID"

                p.Clear()
                p.Add(New SqlParameter("@Period_ID", PeriodBox.zSelectedPeriod_ID))
                p.Add(New SqlParameter("@Allocation_ID", AllocationBox.SelectedValue))
                p.Add(New SqlParameter("@RID", ProviderBox.zSelectedProvider.RID))

                RunNonQueryParams(sql, p)

                sql = "update provider_Patterns set RID = @RID where RID = @SafeRid"
                p.Clear()
                p.Add(New SqlParameter("@RID", ProviderBox.zSelectedProvider.RID))
                p.Add(New SqlParameter("@SafeRID", SafetyRID))

                RunNonQueryParams(sql, p)


                UpdateProviderPayments(ProviderBox.zSelectedProvider.RID)
            Else

                'delete the pattern you just added
                sql = "delete from Provider_patterns where Period_ID = @Period_ID and Allocation_ID = @Allocation_ID and RID = @RID"

                p.Clear()
                p.Add(New SqlParameter("@Period_ID", PeriodBox.zSelectedPeriod_ID))
                p.Add(New SqlParameter("@Allocation_ID", AllocationBox.SelectedValue))
                p.Add(New SqlParameter("@RID", ProviderBox.zSelectedProvider.RID))

                RunNonQueryParams(sql, p)
            End If

            .Close()
        End With

        If Errors = "" Then
            LoadAndBindProvPatterns()

        End If


    End Sub

    Private Sub EditProviderPattern()
        Dim Errors As String = ""

        'check first if everything has already been paid
        'Dim FrozenPayments As Decimal = 0
        'Dim TotalPayments As Decimal = 0
        Dim r As DataRowView

        'For Each r In ProviderPatternRepeater.DataSource
        '    If r("Frozen") = 1 Then

        '        FrozenPayments = FrozenPayments + r("Month_Total")

        '    End If
        '    TotalPayments = TotalPayments + r("Month_Total")
        'Next

        'If FrozenPayments >= TotalPayments Then

        '    MessageBox.Show("All possible payments have been frozen.  If you need to update the pattern either unfreeze payments or update the allocation first", "Cannot edit pattern", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        '    Exit Sub
        'End If

        With PatternDialogV2
            .Period_ID = PeriodBox.zSelectedPeriod_ID
            .Allocation_ID = AllocationBox.SelectedValue
            .Payment_ID = ""
            .RID = ProviderBox.zSelectedProvider.RID
            .IsCustom = False

            If .ShowDialog() = Windows.Forms.DialogResult.OK Then


                Dim p As New List(Of SqlParameter)
                Dim sql As String

                Dim SafetyRID As String = Format(Now(), "ddhhmmssfff")

                For Each r In .TheRepeater.DataSource


                    sql = "insert into Provider_Patterns (Period_ID, Allocation_ID, RID, Month, Percentage) Values (@Period_ID, @Allocation_ID, @RID, @Month, @Percentage)"

                    p.Clear()
                    p.Add(New SqlParameter("@Period_ID", PeriodBox.zSelectedPeriod_ID))
                    p.Add(New SqlParameter("@Allocation_ID", AllocationBox.SelectedValue))
                    p.Add(New SqlParameter("@RID", SafetyRID))
                    p.Add(New SqlParameter("Month", r("Month")))
                    p.Add(New SqlParameter("Percentage", r("Percentage_String").ToString.Replace("%", "") / 100))

                    Errors = RunNonQueryParams(sql, p)

                    If Errors <> "" Then

                        sql = "delete from provider_patterns where RID = '" & SafetyRID & "'"
                        RunNonQuery(sql)

                        MessageBox.Show(Errors, "There was an error", MessageBoxButtons.OK, MessageBoxIcon.Error)

                        .Close()
                        Exit Sub

                    End If


                Next

                'all good so replace
                sql = "delete from Provider_patterns where Period_ID = @Period_ID and Allocation_ID = @Allocation_ID and RID = @RID"

                p.Clear()
                p.Add(New SqlParameter("@Period_ID", PeriodBox.zSelectedPeriod_ID))
                p.Add(New SqlParameter("@Allocation_ID", AllocationBox.SelectedValue))
                p.Add(New SqlParameter("@RID", ProviderBox.zSelectedProvider.RID))

                RunNonQueryParams(sql, p)

                sql = "update provider_Patterns set RID = @RID where RID = @SafeRid"
                p.Clear()
                p.Add(New SqlParameter("@RID", ProviderBox.zSelectedProvider.RID))
                p.Add(New SqlParameter("@SafeRID", SafetyRID))

                RunNonQueryParams(sql, p)


                UpdateProviderPayments(ProviderBox.zSelectedProvider.RID)

            End If

            .Close()
        End With

        If Errors = "" Then
            LoadAndBindProvPatterns()

        End If


    End Sub


    Private Sub UpdateProviderPayments(ByVal RID As String)
        Dim RIDList As New List(Of String)

        RIDList.Clear()
        RIDList.Add(RID)

        If RIDList.Count <> 0 Then
            Dim p As New List(Of Object)
            p.Clear()

            p.Add(PeriodBox.zSelectedPeriod_ID)
            p.Add(AllocationBox.SelectedValue)
            p.Add(RIDList)

            DoSomeWork("UpdateActivePayments", p, False)
        End If

    End Sub

    Private Sub UpdateNationalPayments()

        Dim RIDList As New List(Of String)

        RIDList.Clear()

        Dim DV As DataView
        Dim sql As String

        sql = "select RID from " & _
            "(select distinct RID from Active_Payments where Period_ID = " & PeriodBox.zSelectedPeriod_ID & " and Allocation_ID = '" & AllocationBox.SelectedValue & "' UNION " & _
            "select distinct RID from Allocations where Period_ID = " & PeriodBox.zSelectedPeriod_ID & " and Allocation_ID = '" & AllocationBox.SelectedValue & "') a " & _
            "where a.RID not in (select distinct RID from Provider_Patterns where Period_ID = " & PeriodBox.zSelectedPeriod_ID & " and Allocation_ID = '" & AllocationBox.SelectedValue & "') "

        DV = LoadDataView(sql)

        Dim r As DataRowView

        For Each r In DV
            RIDList.Add(r("RID"))
        Next

        If RIDList.Count <> 0 Then
            Dim p As New List(Of Object)
            p.Clear()

            p.Add(PeriodBox.zSelectedPeriod_ID)
            p.Add(AllocationBox.SelectedValue)
            p.Add(RIDList)

            DoSomeWork("UpdateActivePayments", p, False)
        End If

    End Sub

    Private Sub DeleteProviderPattern()

        If MessageBox.Show("Are you sure you want to delete this provider pattern and revert back to the National Pattern (All frozen payments will not be affected)", "Delete Provider Pattern?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

            Dim sql As String
            Dim p As New List(Of SqlParameter)


            sql = "delete from Provider_patterns where Period_ID = @Period_ID and Allocation_ID = @Allocation_ID and RID = @RID"

            p.Clear()
            p.Add(New SqlParameter("@Period_ID", PeriodBox.zSelectedPeriod_ID))
            p.Add(New SqlParameter("@Allocation_ID", AllocationBox.SelectedValue))
            p.Add(New SqlParameter("@RID", ProviderBox.zSelectedProvider.RID))

            RunNonQueryParams(sql, p)

            UpdateProviderPayments(ProviderBox.zSelectedProvider.RID)

            LoadAndBindProvPatterns()

        End If

    End Sub


    Private Sub EditCustomPattern()
        Dim Errors As String = ""

        With PatternDialogV2
            .Period_ID = PeriodBox.zSelectedPeriod_ID
            .Allocation_ID = AllocationBox.SelectedValue
            .Payment_ID = CurrentPaymentID
            .RID = ""
            .IsCustom = True

            If .ShowDialog() = Windows.Forms.DialogResult.OK Then

                Dim r As DataRowView
                Dim p As New List(Of SqlParameter)
                Dim sql As String

                For Each r In .TheRepeater.DataSource

                  
                        sql = "update Patterns set Percentage = @Percentage where Pattern_ID = @Pattern_ID and Month = @Month"

                        p.Clear()
                        p.Add(New SqlParameter("Pattern_ID", r("Pattern_ID")))
                        p.Add(New SqlParameter("Month", r("Month")))
                        p.Add(New SqlParameter("Percentage", r("Percentage_String").ToString.Replace("%", "") / 100))

                        Errors = RunNonQueryParams(sql, p)

                        If Errors <> "" Then
                            MessageBox.Show(Errors, "There was an error", MessageBoxButtons.OK, MessageBoxIcon.Error)

                            .Close()
                            Exit Sub

                        End If


                Next

            End If

            .Close()
        End With

        If Errors = "" Then
            LoadAndBindCustomRepeater()
        End If
    End Sub

    Private Sub DeleteCustomPattern()

        If MessageBox.Show("Are you sure you would like to delete the selected custom pattern? (This will not affect any funding)", "Delete Custom Pattern?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

            Dim sql As String

            sql = "delete from Patterns where Pattern_ID = '" & CustomPatternBox.SelectedValue & "'"
            RunNonQuery(sql)

            sql = "delete from pattern_IDs where Pattern_ID = '" & CustomPatternBox.SelectedValue & "'"
            RunNonQuery(sql)

            LoadCustomBox()


        End If

    End Sub

    Private Sub AddBudget()
        Dim sql As String
        Dim DV As DataView

        sql = "select top 1 code from ( " & _
                "select 'A' as code " & _
                "union select 'B' as code " & _
                "union select 'C' as code " & _
                "union select 'D' as code " & _
                "union select 'E' as code " & _
                "union select 'F' as code " & _
                "union select 'G' as code " & _
                "union select 'H' as code " & _
                "union select 'I' as code " & _
                "union select 'J' as code " & _
                "union select 'K' as code " & _
                "union select 'L' as code " & _
                "union select 'M' as code " & _
                "union select 'N' as code " & _
                "union select 'O' as code " & _
                "union select 'P' as code " & _
                "union select 'Q' as code " & _
                "union select 'R' as code " & _
                "union select 'S' as code " & _
                "union select 'T' as code " & _
                "union select 'U' as code " & _
                "union select 'V' as code " & _
                "union select 'W' as code " & _
                "union select 'X' as code " & _
                "union select 'Y' as code " & _
                "union select 'Z' as code) alpha " & _
                "where alpha.code not in (select substring(Budget_ID,5,1) as code from Budgets)"

        DV = LoadDataView(sql)

        If DV.Count = 0 Then
            MessageBox.Show("There are no codes left to add any more budgets", "Cannot add any more budgets", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End If

        Dim TheCode As String = "YPLR" & DV(0)("code").ToString

        Dim Description As String = "_REPEAT"

        While Description = "_REPEAT"
            Description = InputBox("Please enter a description for the new budget", "Adding budget...", "")

            If Len(Description) > 50 Then
                Description = "_REPEAT"
                MessageBox.Show("The description cannot be more than 50 characters long, please enter a description that is not more than 50 characters long.", "Please try again", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
        End While

        If Description = "" Then Exit Sub

        sql = "insert into Budgets (Budget_ID, Budget_Description) values(@Budget_ID, @Description)"

        Dim p As New List(Of SqlParameter)

        p.Clear()
        p.Add(New SqlParameter("Budget_ID", TheCode))
        p.Add(New SqlParameter("Description", Description))

        If RunNonQueryParams(sql, p) = "" Then
            LoadBudgetBox()
            BudgetBox.SelectedValue = TheCode
            NationalRepeater.DataSource = Nothing
            ProviderPatternRepeater.DataSource = Nothing

        End If


    End Sub

    Private Sub EditBudget()

        Dim TheCode As String = BudgetBox.SelectedValue

        Dim Description As String = "_REPEAT"

        While Description = "_REPEAT"
            Description = InputBox("Please enter a description for the budget", "Edit budget...", DirectCast(BudgetBox.DataSource, DataView)(BudgetBox.SelectedIndex)("Budget_Description").ToString)

            If Len(Description) > 50 Then
                Description = "_REPEAT"
                MessageBox.Show("The description cannot be more than 50 characters long, please enter a description that is not more than 50 characters long.", "Please try again", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
        End While

        If Description = "" Then Exit Sub

        Dim sql As String
        sql = "Update Budgets set Budget_Description = @Description where Budget_ID = @Budget_ID"

        Dim p As New List(Of SqlParameter)

        p.Clear()
        p.Add(New SqlParameter("Budget_ID", TheCode))
        p.Add(New SqlParameter("Description", Description))

        If RunNonQueryParams(sql, p) = "" Then
            LoadBudgetBox()
            BudgetBox.SelectedValue = TheCode

        End If



    End Sub

    Private Sub DeleteBudget()

        'check if there are any allocation lines attached to this budget

        Dim DV As DataView
        Dim sql As String

        sql = "select * from allocation_lines where Budget_ID = '" & BudgetBox.SelectedValue & "'"

        DV = LoadDataView(sql)

        If DV.Count = 0 Then

            If MessageBox.Show("Are you sure you want to delete the selected budget?", "Delete budget?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

                sql = "delete from Budgets where Budget_ID = '" & BudgetBox.SelectedValue & "'"
                RunNonQuery(sql)
                LoadBudgetBox()

            End If

        Else
            MessageBox.Show("You cannot delete this budget, because it has allocation lines linked to it", "Cannot delete budget", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If


    End Sub

    Private Sub AddAllocationLine()

        Dim TheCode As String = Mid(BudgetBox.SelectedValue, 5, 1)

        Dim Description As String = "_REPEAT"

        While Description = "_REPEAT"
            Description = InputBox("Please enter a description for the allocation line", "Add Allocation...", "")

            If Len(Description) > 50 Then
                Description = "_REPEAT"
                MessageBox.Show("The description cannot be more than 50 characters long, please enter a description that is not more than 50 characters long.", "Please try again", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
        End While

        If Description = "" Then Exit Sub

        Dim DV As DataView
        Dim sql As String

        sql = "select isnull(max(cast(SUBSTRING(Allocation_ID,4,2) as int)),0) as code from Allocation_Lines where Allocation_ID like 'YP" & TheCode & "%'"
        DV = LoadDataView(sql)

        Dim Allocation_ID As String

        Allocation_ID = DV(0)("code").ToString + 1

        If Len(Allocation_ID) < 2 Then
            Allocation_ID = "0" & Allocation_ID
        End If

        Allocation_ID = "YP" & TheCode & Allocation_ID

        sql = "insert into Allocation_Lines (Allocation_ID, Budget_ID, Allocation_Description, Field_Code, Hidden) values(@Allocation_ID, @Budget_ID, @Allocation_Description, @Field_Code, @Hidden)"

        Dim p As New List(Of SqlParameter)

        p.Clear()
        p.Add(New SqlParameter("Allocation_ID", Allocation_ID))
        p.Add(New SqlParameter("Budget_ID", BudgetBox.SelectedValue))
        p.Add(New SqlParameter("Allocation_Description", Description))
        p.Add(New SqlParameter("Field_Code", 0))
        p.Add(New SqlParameter("Hidden", 0))

        If RunNonQueryParams(sql, p) = "" Then
            LoadAllocationBox()
            AllocationBox.SelectedValue = Allocation_ID
        End If

    End Sub

    Private Sub EditAllocation()


        Dim Description As String = "_REPEAT"

        While Description = "_REPEAT"
            Description = InputBox("Please enter a description for the allocation line", "Edit Allocation...", DirectCast(AllocationBox.DataSource, DataView)(AllocationBox.SelectedIndex)("Allocation_Description").ToString)

            If Len(Description) > 50 Then
                Description = "_REPEAT"
                MessageBox.Show("The description cannot be more than 50 characters long, please enter a description that is not more than 50 characters long.", "Please try again", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
        End While

        If Description = "" Then Exit Sub
        Dim sql As String
        Dim Allocation_ID As String

        Allocation_ID = AllocationBox.SelectedValue


        sql = "Update Allocation_Lines set Allocation_Description = @Allocation_Description where Allocation_ID = @Allocation_ID"

        Dim p As New List(Of SqlParameter)

        p.Clear()
        p.Add(New SqlParameter("Allocation_ID", Allocation_ID))
        p.Add(New SqlParameter("Allocation_Description", Description))
      
        If RunNonQueryParams(sql, p) = "" Then
            LoadAllocationBox()
            AllocationBox.SelectedValue = Allocation_ID
        End If

    End Sub

    Private Sub DeleteAllocation()

        'check if there are any patterns for this allocation

        Dim DV As DataView
        Dim sql As String

        sql = "select * from Pattern_IDs where Allocation_ID = '" & AllocationBox.SelectedValue & "'"

        DV = LoadDataView(sql)

        If DV.Count = 0 Then

            If MessageBox.Show("Are you sure you want to delete the selected allocation?", "Delete allocation?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

                sql = "delete from Allocation_Lines where Allocation_ID = '" & AllocationBox.SelectedValue & "'"
                RunNonQuery(sql)
                LoadAllocationBox()

            End If

        Else
            MessageBox.Show("You cannot delete this allocation line, because it has patterns linked to it", "Cannot delete Allocation Line", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If


    End Sub

    Private Sub EditProviderItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EditProviderItem.Click
        EditProviderPattern()

    End Sub

    Private Sub EditCustomPatternItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EditCustomPatternItem.Click
        EditCustomPattern()

    End Sub

    Private Sub AddProviderItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddProviderItem.Click
        AddProviderPattern()
    End Sub

    Private Sub DeleteProviderItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteProviderItem.Click
        DeleteProviderPattern()

    End Sub

    Private Sub DeleteCustomPatternItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteCustomPatternItem.Click
        DeleteCustomPattern()

    End Sub

    Private Sub AddToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddToolStripMenuItem.Click
        AddBudget()
    End Sub

    Private Sub AllocationMenu_Opening(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles AllocationMenu.Opening
        If AllocationBox.Items.Count = 0 Then
            AddAllocationMenuItem.Visible = True
            EditAllocationMenuItem.Visible = False
            DeleteAllocationMenuItem.Visible = False

        Else
            AddAllocationMenuItem.Visible = True
            EditAllocationMenuItem.Visible = True
            DeleteAllocationMenuItem.Visible = True
        End If

    End Sub

    Private Sub EditToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EditToolStripMenuItem.Click
        EditBudget()

    End Sub

    Private Sub DeleteToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteToolStripMenuItem.Click
        DeleteBudget()

    End Sub

    Private Sub AddAllocationMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddAllocationMenuItem.Click
        AddAllocationLine()

    End Sub

    Private Sub EditAllocationMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EditAllocationMenuItem.Click
        EditAllocation()

    End Sub

    Private Sub DeleteAllocationMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteAllocationMenuItem.Click
        DeleteAllocation()

    End Sub

    Private Sub ApplyCustomPatternItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ApplyCustomPatternItem.Click
        With ApplyCustomPatternsForm
            .MdiParent = MainForm
            .Dock = DockStyle.Fill

            .HeaderLabel.Text = DirectCast(CustomPatternBox.DataSource, DataView)(CustomPatternBox.SelectedIndex)("Pattern_Description")
            .Show()
        End With

        
    End Sub

    Private Sub FreezeMenu_Opening(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles FreezeMenu.Opening

        If NationalRepeater.CurrentItemIndex = -1 Then
            e.Cancel = True
        End If

        Dim Frozen As Integer

        Frozen = DirectCast(NationalRepeater.DataSource, DataView)(NationalRepeater.CurrentItemIndex)("Frozen")

        If Frozen = 1 Then
            UnfreezePaymentsMenuItem.Visible = True
            FreezePaymentsMenuItem.Visible = False
            FreezeBudgetMenuItem.Visible = False

            UnfreezePaymentsMenuItem.Text = "Unfreeze Payments in Allocation down to " & DirectCast(NationalRepeater.DataSource, DataView)(NationalRepeater.CurrentItemIndex)("Friendly_Date")
        Else
            UnfreezePaymentsMenuItem.Visible = False
            FreezePaymentsMenuItem.Visible = True
            FreezeBudgetMenuItem.Visible = True

            FreezePaymentsMenuItem.Text = "Freeze Payments in Allocation up to " & DirectCast(NationalRepeater.DataSource, DataView)(NationalRepeater.CurrentItemIndex)("Friendly_Date")
            FreezeBudgetMenuItem.Text = "Freeze Payments in Budget up to " & DirectCast(NationalRepeater.DataSource, DataView)(NationalRepeater.CurrentItemIndex)("Friendly_Date")

        End If



    End Sub

    Private Sub FreezePayments()

        If MessageBox.Show("Are you sure you want to freeze payments", "Freeze payments?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        'do the rid list

        Dim RIDList As New List(Of String)
        RIDList.Clear()

        Dim RIDView As DataView

        RIDView = LoadDataView("select distinct RID from Allocations where status = 'Approved' and Period_ID = " & PeriodBox.zSelectedPeriod_ID & " and Allocation_ID = '" & AllocationBox.SelectedValue & "'")

        Dim rec As DataRowView

        For Each rec In RIDView
            RIDList.Add(rec("RID"))
        Next


        Dim p As New List(Of Object)
        p.Clear()

        p.Add(PeriodBox.zSelectedPeriod_ID) 'period
        p.Add(AllocationBox.SelectedValue) 'allocation
        p.Add(RIDList) 'list of rids
        p.Add(DirectCast(NationalRepeater.DataSource, DataView)(NationalRepeater.CurrentItemIndex)("Month"))

        DoSomeWork("FreezePayments", p, False)

        LoadAndBindRepeater()
        LoadAndBindProvPatterns()
    End Sub

    Private Sub FreezePaymentsBudget()

        If MessageBox.Show("Are you sure you want to freeze payments", "Freeze payments?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        'do the rid list

        Dim RIDList As New List(Of String)
        RIDList.Clear()

        Dim RIDView As DataView

        RIDView = LoadDataView("select distinct RID from Allocations where status = 'Approved' and Period_ID = " & PeriodBox.zSelectedPeriod_ID & " and Allocation_ID in (Select Allocation_ID from allocation_lines where budget_id = '" & BudgetBox.SelectedValue & "')")

        Dim rec As DataRowView

        For Each rec In RIDView
            RIDList.Add(rec("RID"))
        Next


        Dim p As New List(Of Object)
        p.Clear()

        p.Add(PeriodBox.zSelectedPeriod_ID) 'period
        p.Add(BudgetBox.SelectedValue) 'Budget
        p.Add(RIDList) 'list of rids
        p.Add(DirectCast(NationalRepeater.DataSource, DataView)(NationalRepeater.CurrentItemIndex)("Month"))

        DoSomeWork("FreezePaymentsBudget", p, False)

        LoadAndBindRepeater()
        LoadAndBindProvPatterns()
    End Sub

    Private Sub FreezePaymentsMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FreezePaymentsMenuItem.Click
        FreezePayments()

    End Sub

    Private Sub UnfreezeProviderPayments()
        If MessageBox.Show("Are you sure you want to unfreeze payments", "Unfreeze payments?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        'do the rid list

        Dim RIDList As New List(Of String)
        RIDList.Clear()

        RIDList.Add(ProviderBox.zSelectedProvider.RID)

        Dim p As New List(Of Object)
        p.Clear()

        p.Add(PeriodBox.zSelectedPeriod_ID) 'period
        p.Add(AllocationBox.SelectedValue) 'allocation
        p.Add(RIDList) 'list of rids
        p.Add(DirectCast(ProviderPatternRepeater.DataSource, DataView)(ProviderPatternRepeater.CurrentItemIndex)("Month"))

        DoSomeWork("UnfreezePayments", p, False)

        LoadAndBindRepeater()
        LoadAndBindProvPatterns()
    End Sub

    Private Sub FreezeProviderPayments()
        If MessageBox.Show("Are you sure you want to freeze payments", "Freeze payments?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        'do the rid list

        Dim RIDList As New List(Of String)
        RIDList.Clear()

        RIDList.Add(ProviderBox.zSelectedProvider.RID)

        Dim p As New List(Of Object)
        p.Clear()

        p.Add(PeriodBox.zSelectedPeriod_ID) 'period
        p.Add(AllocationBox.SelectedValue) 'allocation
        p.Add(RIDList) 'list of rids
        p.Add(DirectCast(ProviderPatternRepeater.DataSource, DataView)(ProviderPatternRepeater.CurrentItemIndex)("Month"))

        DoSomeWork("FreezePayments", p, False)

        LoadAndBindRepeater()
        LoadAndBindProvPatterns()
    End Sub

    Private Sub Unfreezepayments()

        If MessageBox.Show("Are you sure you want to unfreeze payments", "Unfreeze payments?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        'do the rid list

        Dim RIDList As New List(Of String)
        RIDList.Clear()

        Dim RIDView As DataView

        RIDView = LoadDataView("select distinct RID from Payments where Period_ID = " & PeriodBox.zSelectedPeriod_ID & " and Allocation_ID = '" & AllocationBox.SelectedValue & "'")

        Dim rec As DataRowView

        For Each rec In RIDView
            RIDList.Add(rec("RID"))
        Next


        Dim p As New List(Of Object)
        p.Clear()

        p.Add(PeriodBox.zSelectedPeriod_ID) 'period
        p.Add(AllocationBox.SelectedValue) 'allocation
        p.Add(RIDList) 'list of rids
        p.Add(DirectCast(NationalRepeater.DataSource, DataView)(NationalRepeater.CurrentItemIndex)("Month"))

        DoSomeWork("UnfreezePayments", p, False)

        LoadAndBindRepeater()
        LoadAndBindProvPatterns()

    End Sub

    Private Sub UnfreezePaymentsMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UnfreezePaymentsMenuItem.Click
        Unfreezepayments()

    End Sub

    Private Sub FreezeBudgetMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FreezeBudgetMenuItem.Click
        FreezePaymentsBudget()

    End Sub

    Private Sub ProviderFreezeMenu_Opening(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles ProviderFreezeMenu.Opening
        If ProviderPatternRepeater.CurrentItemIndex = -1 Then
            e.Cancel = True
        End If

        Dim Frozen As Integer

        Frozen = DirectCast(ProviderPatternRepeater.DataSource, DataView)(ProviderPatternRepeater.CurrentItemIndex)("Frozen")

        If Frozen = 0 Then
            ProviderFreezeMenuItem.Text = "Freeze Payments in Allocation up to " & DirectCast(ProviderPatternRepeater.DataSource, DataView)(ProviderPatternRepeater.CurrentItemIndex)("Friendly_Date")
            ProviderUnfreezeMenuItem.Visible = False
            ProviderFreezeMenuItem.Visible = True


        Else
            ProviderUnfreezeMenuItem.Text = "Unfreeze Payments in Allocation down to " & DirectCast(ProviderPatternRepeater.DataSource, DataView)(ProviderPatternRepeater.CurrentItemIndex)("Friendly_Date")
            ProviderFreezeMenuItem.Visible = False
            ProviderUnfreezeMenuItem.Visible = True

        End If


        
        

    End Sub

    Private Sub ProviderUnfreezeMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProviderUnfreezeMenuItem.Click
        UnfreezeProviderPayments()

    End Sub

    Private Sub ProviderFreezeMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProviderFreezeMenuItem.Click
        FreezeProviderPayments()

    End Sub
End Class